﻿
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using OpenLab.Model;

namespace OpenLab.DAL
{
	public partial class PrixAnalyseOrganismeDAL
	{
        public PrixAnalyseOrganisme Add
			(PrixAnalyseOrganisme prixAnalyseOrganisme)
		{
				string sql ="INSERT INTO PrixAnalyseOrganisme (idanalyse, idorganisme, prixtotal, partpatient, partorganisme)  output inserted.id VALUES (@idanalyse, @idorganisme, @prixtotal, @partpatient, @partorganisme)";
				SqlParameter[] para = new SqlParameter[]
					{
						new SqlParameter("@idanalyse", ToDBValue(prixAnalyseOrganisme.idanalyse)),
						new SqlParameter("@idorganisme", ToDBValue(prixAnalyseOrganisme.idorganisme)),
						new SqlParameter("@prixtotal", ToDBValue(prixAnalyseOrganisme.prixtotal)),
						new SqlParameter("@partpatient", ToDBValue(prixAnalyseOrganisme.partpatient)),
						new SqlParameter("@partorganisme", ToDBValue(prixAnalyseOrganisme.partorganisme)),
					};
					
				int newId = (int)SqlHelper.ExecuteScalar(sql, para);
				return GetById(newId);
		}

        public int DeleteById(int id)
		{
            string sql = "DELETE PrixAnalyseOrganisme WHERE Id = @Id";

           SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@id", id)
			};
		
            return SqlHelper.ExecuteNonQuery(sql, para);
		}
		
				
        public int Update(PrixAnalyseOrganisme prixAnalyseOrganisme)
        {
            string sql =
                "UPDATE PrixAnalyseOrganisme " +
                "SET " +
			" idanalyse = @idanalyse" 
                +", idorganisme = @idorganisme" 
                +", prixtotal = @prixtotal" 
                +", partpatient = @partpatient" 
                +", partorganisme = @partorganisme" 
               
            +" WHERE id = @id";


			SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@id", prixAnalyseOrganisme.id)
					,new SqlParameter("@idanalyse", ToDBValue(prixAnalyseOrganisme.idanalyse))
					,new SqlParameter("@idorganisme", ToDBValue(prixAnalyseOrganisme.idorganisme))
					,new SqlParameter("@prixtotal", ToDBValue(prixAnalyseOrganisme.prixtotal))
					,new SqlParameter("@partpatient", ToDBValue(prixAnalyseOrganisme.partpatient))
					,new SqlParameter("@partorganisme", ToDBValue(prixAnalyseOrganisme.partorganisme))
			};

			return SqlHelper.ExecuteNonQuery(sql, para);
        }		
		
        public PrixAnalyseOrganisme GetById(int id)
        {
            string sql = "SELECT * FROM PrixAnalyseOrganisme WHERE Id = @Id";
            using(SqlDataReader reader = SqlHelper.ExecuteDataReader(sql, new SqlParameter("@Id", id)))
			{
				if (reader.Read())
				{
					return ToModel(reader);
				}
				else
				{
					return null;
				}
       		}
        }

        public IEnumerable<PrixAnalyseOrganisme> GetByIdOrganisme(int idorganisme)
        {
            string sql = "SELECT * FROM PrixAnalyseOrganisme WHERE idorganisme = @idorganisme";
            using (SqlDataReader reader = SqlHelper.ExecuteDataReader(sql, new SqlParameter("@idorganisme", idorganisme)))
			{
				if (reader.Read())
				{
					return ToModels(reader);
				}
				else
				{
					return null;
				}
       		}
        }
		public PrixAnalyseOrganisme ToModel(SqlDataReader reader)
		{
			PrixAnalyseOrganisme prixAnalyseOrganisme = new PrixAnalyseOrganisme();

			prixAnalyseOrganisme.id = (int)ToModelValue(reader,"id");
			prixAnalyseOrganisme.idanalyse = (int)ToModelValue(reader,"idanalyse");
			prixAnalyseOrganisme.idorganisme = (int)ToModelValue(reader,"idorganisme");
			prixAnalyseOrganisme.prixtotal = (decimal)ToModelValue(reader,"prixtotal");
			prixAnalyseOrganisme.partpatient = (decimal)ToModelValue(reader,"partpatient");
			prixAnalyseOrganisme.partorganisme = (decimal)ToModelValue(reader,"partorganisme");
			return prixAnalyseOrganisme;
		}
		
		public int GetTotalCount()
		{
			string sql = "SELECT count(*) FROM PrixAnalyseOrganisme";
			return (int)SqlHelper.ExecuteScalar(sql);
		}
		
		public IEnumerable<PrixAnalyseOrganisme> GetPagedData(int minrownum,int maxrownum)
		{
			string sql = "SELECT * from(SELECT *,row_number() over(order by id) rownum FROM PrixAnalyseOrganisme) t where rownum>=@minrownum and rownum<=@maxrownum";
			using(SqlDataReader reader = SqlHelper.ExecuteDataReader(sql,
				new SqlParameter("@minrownum",minrownum),
				new SqlParameter("@maxrownum",maxrownum)))
			{
				return ToModels(reader);					
			}
		}
		
		public IEnumerable<PrixAnalyseOrganisme> GetAll()
		{
			string sql = "SELECT * FROM PrixAnalyseOrganisme";
			using(SqlDataReader reader = SqlHelper.ExecuteDataReader(sql))
			{
				return ToModels(reader);			
			}
		}
		
		protected IEnumerable<PrixAnalyseOrganisme> ToModels(SqlDataReader reader)
		{
			var list = new List<PrixAnalyseOrganisme>();
			while(reader.Read())
			{
				list.Add(ToModel(reader));
			}	
			return list;
		}		
		
		protected object ToDBValue(object value)
		{
			if(value==null)
			{
				return DBNull.Value;
			}
			else
			{
				return value;
			}
		}
		
		protected object ToModelValue(SqlDataReader reader,string columnName)
		{
			if(reader.IsDBNull(reader.GetOrdinal(columnName)))
			{
				return null;
			}
			else
			{
				return reader[columnName];
			}
		}
	}
}
